﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using System.Data.SqlClient;	// MSSQL Interface

namespace GE_Database_Manager
{
	class Standardized : Database
	{
		private SqlConnection _connection;
		private Standardized_Wrapper _wrapper;
		private string _serverName, _databaseName, _key;
        private int progressStep;

		public Standardized(SqlConnection conn, string serverName, string databaseName)
		{
			_connection = conn;
			_serverName = serverName;
			_databaseName = databaseName;

			_key = _connection.ConnectionString + _databaseName;
		}

		#region Getters and Setters
		public SqlConnection Connection
		{
			get { return _connection; }
			set { }
		}
        public override int ProgressStep
        {
            get { return progressStep; }
            set { progressStep = value; }
        }

		public string ServerName
		{
			get { return _serverName; }
			set { }
		}

		public string DatabaseName
		{
			get { return _databaseName; }
			set { }
		}

		public override string Key
		{
			get { return _key; }
			set { }
		}
		#endregion

		public override bool Process(Standardized_Wrapper wrapper)
		{
            // set the database name
            wrapper.CurrentDB = "STANDARDIZED";

			_wrapper = wrapper;

			// Get and insert machines
			Dictionary<int, int> machines = ProcessMachines();

			// Get and insert machine conditions
			ProcessMachineConditions(ref machines);

			// Get and insert stages
			Dictionary<int, int> stages = ProcessStages(ref machines);

			// Get and insert sensors
			Dictionary<int, int> sensors = ProcessSensors(ref stages);

			// Get and insert waveforms
			ProcessWaveforms(ref sensors);

			return false;
		}

		private Dictionary<int, int> ProcessMachines()
		{
			Dictionary<int, int> machines = new Dictionary<int, int>();

			DataTable machineTable = null;
			ExecuteStoredProcedure("GetMachines", ref machineTable);

			foreach (DataRow machine in machineTable.Rows)
			{
				int newMachineId = _wrapper.InsertMachine(
					(string)machine["serial_number"],
					(string)machine["type"],
					(string)machine["site_location"],
					(machine["tag"] == DBNull.Value ? null : (string)machine["tag"]),
					(string)machine["workorder"],
					(string)machine["make"]);

				machines.Add((int)machine["machine_id"], newMachineId);
			}

			return machines;
		}

		private bool ProcessMachineConditions(ref Dictionary<int, int> machines)
		{
			DataTable conditions = null;
			ExecuteStoredProcedure("GetMachineConditions", ref conditions);

			foreach (DataRow condition in conditions.Rows)
			{
				int outMachineId = -1;
				machines.TryGetValue(((int)condition["machine_id"]), out outMachineId);

				bool inserted = _wrapper.InsertMachineCondition(
					outMachineId,
					(DateTime)condition["start_date"],
					(DateTime)condition["end_date"],
					(string)condition["condition"],
					(string)condition["comments"],
					(int)condition["data_source_id"]
					);
			}
			return true;
		}

		private Dictionary<int, int> ProcessStages(ref Dictionary<int, int> machines)
		{
			Dictionary<int, int> stages = new Dictionary<int, int>();

			DataTable stageTable = null;
			ExecuteStoredProcedure("GetStages", ref stageTable);

			foreach (DataRow stage in stageTable.Rows)
			{
				int outMachineId = -1;
				machines.TryGetValue(((int)stage["machine_id"]), out outMachineId);

				Designation des = (int)stage["designation"] == 0 ? Designation.Parallel : Designation.Planetary;
				int? fix = stage["fixed"] == DBNull.Value ? null : (int?)stage["fixed"];
				Fixed? fixedGear = (Fixed?)fix;

				int inserted = _wrapper.InsertStage(
					des,
					outMachineId,
					fixedGear,
					(stage["gear_tooth_count"] == DBNull.Value ? null : (int?)stage["gear_tooth_count"]),
					(stage["pinion_tooth_count"] == DBNull.Value ? null : (int?)stage["pinion_tooth_count"]),
					(stage["sun_tooth_count"] == DBNull.Value ? null : (int?)stage["sun_tooth_count"]),
					(stage["planet_tooth_count"] == DBNull.Value ? null : (int?)stage["planet_tooth_count"]),
					(stage["ring_tooth_count"] == DBNull.Value ? null : (int?)stage["ring_tooth_count"]),
					(stage["planet_count"] == DBNull.Value ? null : (int?)stage["planet_count"])
					);

				stages.Add((int)stage["stage_id"], inserted);
			}
			return stages;
		}

		private Dictionary<int, int> ProcessSensors(ref Dictionary<int, int> machines)
		{
			Dictionary<int, int> sensors = new Dictionary<int, int>();

			DataTable dbSensors = null;
			ExecuteStoredProcedure("GetSensors", ref dbSensors);

			foreach (DataRow sensor in dbSensors.Rows)
			{
				int outStageId = -1;
				machines.TryGetValue((int)sensor["stage_id"], out outStageId);

				int sensorId = _wrapper.InsertSensor(
					(string)sensor["point_name"],
					outStageId
					);

				sensors.Add((int)sensor["sensor_id"], sensorId);
			}

			return sensors;
		}

		private bool ProcessWaveforms(ref Dictionary<int, int> sensors)
		{
			DataTable waveforms = null;
			ExecuteStoredProcedure("GetWaveforms", ref waveforms);

			foreach(DataRow waveform in waveforms.Rows)
			{
				Single? rpm = waveform["machine_rpm"] == DBNull.Value ? null : (Single?)waveform["machine_rpm"];
				byte[] status = waveform["status"] == DBNull.Value ? null : (byte[])waveform["status"];

				int outSensorId = -1;
				sensors.TryGetValue((int)waveform["sensor_id"], out outSensorId);

				_wrapper.InsertWaveform(
					outSensorId,
					(DateTime)waveform["gmt"],
					rpm,
					(int)waveform["sample_rate"],
					(int)waveform["bits_per_sample"],
					(byte[])waveform["sample_data"],
					(int)waveform["engr_unit_id"],
					status
					);
			}

			return true;
		}

		/// <summary>
		/// Execute the given Stored Procedure within the current database.
		/// </summary>
		/// <param name="storedProcedure">Name of the StoredProcedure (case sensitive)</param>
		/// <param name="tableName"></param>
		/// <param name="table">DataTable to fill.</param>
		/// <returns></returns>
		private bool ExecuteStoredProcedure(string storedProcedure, ref DataTable table)
		{
			try
			{
				_connection.Open();
				using (SqlDataAdapter adapter = new SqlDataAdapter(DatabaseName + ".dbo." + storedProcedure, _connection))
				{
					//adapter.SelectCommand = new SqlCommand(storedProcedure, _connection);
					adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

					DataSet set = new DataSet();
					adapter.Fill(set);

					table = set.Tables[0];
				}
			}
			catch (SqlException sqlEx)
			{
				_connection.Close();
				Console.WriteLine(@"Standardized.cs\\ExecuteStoredProcedure: " + sqlEx.Message);
				return false;
			}
			finally
			{
				_connection.Close();
			}
			return true;
		}
	}
}
